package org.liu.trace.util;

import lombok.SneakyThrows;
import org.liu.trace.Record;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCUtil { // 创建类Conn
    Connection con; // 声明Connection对象
    public static String user;
    public static  String password;
    public Connection getConnection() { // 建立返回值为Connection的方法
        try { // 加载数据库驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        user = "root";//数据库登录名
        password = "lionkliu123";//密码
        try { // 通过访问数据库的URL获取数据库连接对象
            con = DriverManager.getConnection("jdbc:mysql://47.116.68.67:3306/trace_db?characterEncoding=utf-8&serverTimezone=Asia/Shanghai", user, password);
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con; // 按方法要求返回一个Connection对象
    }
    public static void main(String[] args) { // 主方法，测试连接
        JDBCUtil c = new JDBCUtil(); // 创建本类对象
        c.getConnection(); // 调用连接数据库的方法

        List<Record> recordList = getRecordList();
        System.out.println(recordList);
    }

    @SneakyThrows
    public static List<Record> getRecordList(){
        JDBCUtil c = new JDBCUtil();
        Connection connection = c.getConnection();
        Statement statement = connection.createStatement();
        String sql = "SELECT * FROM record;";
        ResultSet resultSet = statement.executeQuery(sql);
        List<Record> records = new ArrayList<>();
        while(resultSet.next()){
            Record record = new Record()
                    .setId(resultSet.getInt("id"))
                    .setProId(resultSet.getInt("pro_id"))
                    .setUserId(resultSet.getInt("user_id"))
                    .setLocation(resultSet.getString("location"))
                    .setCreateTime(resultSet.getDate("create_time"))
                    .setImg(resultSet.getString("img"))
                    .setUpdateTime(resultSet.getDate("update_time"))
                    .setRemarks(resultSet.getString("remarks"))
                    .setDelFlag(resultSet.getString("del_flag"));
            records.add(record);
        }

        resultSet.close();
        statement.close();
        connection.close();
        return records;
    }
}

